class Example < ActiveRecord::Base
  serialize :data
  has_many :assigns
  has_many :dictionaries, :through=>:assigns
  
  def self.make_for(ids,min_words=5,max_words=30,jlpt=0.0,kanji=0.0,estimate=0.0,complexity=0.0,jlpt_weight=1.0,kanji_weight=1.0,estimate_weight=1.0,complexity_weight=1.0,length_weight=1.0)
    #
    raise "Format Error: parameter should be 0.0 ~ 1.0" unless [jlpt_weight,kanji_weight,estimate_weight,complexity_weight,length_weight].all?{|parameter| (0.0..1.0).include? parameter }
    raise "Format Error: parameter should be integer (>=0)" unless min_words < max_words && [min_words,max_words].all? {|parameter| parameter >= 0 }
    #
    keys = ids.join(",")
    target = "temporary_assigns.dictionary_id"
    #
    sql = "CREATE TEMPORARY TABLE temporary_examples AS " +
    #
    "SELECT temporary_assigns.example_id,temporary_assigns.dictionary_id," +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.jlpt_level END)*#{jlpt_weight} + " +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.kanji_level END)*#{kanji_weight}+ " +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.estimate_level END)*#{estimate_weight}+ " +
    "min(examples.complexity)*#{complexity_weight} + " +
    "(CASE WHEN 1 - power((#{max_words+min_words}-2*min(examples.length))/(#{max_words-min_words}+1),2) < 0 THEN 0.0 ELSE 1 - power((#{max_words+min_words}-2*min(examples.length))/(#{max_words-min_words}+1),2) END)*#{length_weight} as support, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN temporary_assigns.text_position ELSE NULL END) as text_position, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN temporary_assigns.sense_position ELSE NULL END) as sense_position, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN examples.data ELSE NULL END) as data " +
    #
    "FROM temporary_assigns " +
    "LEFT OUTER JOIN dictionaries ON dictionaries.id = temporary_assigns.dictionary_id " +
    "LEFT OUTER JOIN examples ON examples.id = temporary_assigns.example_id " +
    #
    "GROUP BY temporary_assigns.example_id,temporary_assigns.dictionary_id " +
    "HAVING " + 
    "#{target} IN (#{keys}) AND " + # TODO targetがindexの場合の挙動をチェック
    "min(CASE WHEN jlpt_level = 0.0 THEN 10 ELSE jlpt_level END) >= #{jlpt} AND " +
    "min(CASE WHEN estimate_level = 0.0 THEN 10 ELSE estimate_level END) >= #{estimate} AND " + 
    "min(CASE WHEN kanji_level = 0.0 THEN 10 ELSE kanji_level END) >= #{kanji} AND "+
    "min(CASE WHEN complexity = 0.0 THEN 10 ELSE complexity END) >= #{complexity} " +
    #
    "ORDER BY dictionary_id ASC,sense_position ASC,support DESC,example_id ASC,text_position ASC;"
    self.connection.execute sql
  end
  
  def backup
        sql = "CREATE TEMPORARY TABLE temporary_examples AS " +
    "SELECT assigns.example_id,assigns.dictionary_id," +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.jlpt_level END)*#{jlpt_weight} + " +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.kanji_level END)*#{kanji_weight}+ " +
    "min(CASE WHEN #{target} IN (#{keys}) THEN 0.0 ELSE dictionaries.estimate_level END)*#{estimate_weight}+ " +
    "min(examples.complexity)*#{complexity_weight} + " +
    "(CASE WHEN 1 - power((#{max_words+min_words}-2*min(examples.length))/(#{max_words-min_words}+1),2) < 0 THEN 0.0 ELSE 1 - power((#{max_words+min_words}-2*min(examples.length))/(#{max_words-min_words}+1),2) END)*#{length_weight} as support, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN assigns.text_position ELSE NULL END) as text_position, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN assigns.sense_position ELSE NULL END) as sense_position, " +
    "max(CASE WHEN #{target} IN (#{keys}) THEN examples.data ELSE NULL END) as data " +
    "FROM assigns " +
    "LEFT OUTER JOIN dictionaries ON dictionaries.id = assigns.dictionary_id " +
    "LEFT OUTER JOIN examples ON examples.id = assigns.example_id " +
    "WHERE assigns.example_id IN (SELECT assigns.example_id FROM assigns WHERE assigns.dictionary_id IN (#{condition}) GROUP BY assigns.example_id) " +
    "GROUP BY assigns.example_id,assigns.dictionary_id " +
    "HAVING " + 
    "#{target} IN (#{keys}) AND " + # TODO targetがindexの場合の挙動をチェック
    "min(CASE WHEN jlpt_level = 0.0 THEN 10 ELSE jlpt_level END) >= #{jlpt} AND " +
    "min(CASE WHEN estimate_level = 0.0 THEN 10 ELSE estimate_level END) >= #{estimate} AND " + 
    "min(CASE WHEN kanji_level = 0.0 THEN 10 ELSE kanji_level END) >= #{kanji} AND "+
    "min(CASE WHEN complexity = 0.0 THEN 10 ELSE complexity END) >= #{complexity} " +
    "ORDER BY dictionary_id ASC,sense_position ASC,support DESC,example_id ASC,text_position ASC;"
  end
  
  def self.drop_temporary
    self.connection.drop_table :temporary_examples
  end
end
